<?php
/**************************************************************************
 *
 *   Copyright 2010 American Public Media Group
 *
 *   This file is part of AIR2.
 *
 *   AIR2 is free software: you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation, either version 3 of the License, or
 *   (at your option) any later version.
 *
 *   AIR2 is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 *
 *   You should have received a copy of the GNU General Public License
 *   along with AIR2.  If not, see <http://www.gnu.org/licenses/>.
 *
 *************************************************************************/

require_once 'Search_Proxy.php';

/**
 * Batch
 *
 * Base class for batches.
 *
 * @property integer $batch_id
 * @property string $batch_uuid
 * @property integer $batch_user_id
 * @property integer $batch_parent_id
 * @property string $batch_name
 * @property string $batch_desc
 * @property string $batch_type
 * @property string $batch_status
 * @property boolean $batch_public_flag
 * @property integer $batch_cre_user
 * @property integer $batch_upd_user
 * @property timestamp $batch_cre_dtim
 * @property timestamp $batch_upd_dtim
 * @property User $User
 * @property Doctrine_Collection $BatchItem
 * @author rcavis
 * @package default
 */
class Batch extends AIR2_Record {
    /* code_master values */
    public static $STATUS_ACTIVE = 'A';
    public static $STATUS_INACTIVE = 'F';
    public static $TYPE_SOURCE = 'S';
    public static $TYPE_USER = 'U';

    /* Describe valid types for batches (and batch items) */
    public static $TYPES = array(
        'S' => array(
            'table'  => 'Source',
            'pkcol'  => 'src_id',
            'uuid'   => 'src_uuid',
            'batch'  => 'BatchSource',
            'search' => 'sources|active-sources',
        ),
        'U' => array(
            'table'  => 'User',
            'pkcol'  => 'user_id',
            'uuid'   => 'user_uuid',
            'batch'  => 'BatchUser',
            'search' => 'users',
        )
    );


    /**
     * Set the table columns
     */
    public function setTableDefinition() {
        $this->setTableName('batch');
        $this->hasColumn('batch_id', 'integer', 4, array(
                'primary' => true,
                'autoincrement' => true,
            ));
        $this->hasColumn('batch_uuid', 'string', 12, array(
                'fixed' => true,
                'notnull' => true,
                'unique' => true,
            ));
        $this->hasColumn('batch_user_id', 'integer', 4, array(
                'notnull' => true,
            ));
        $this->hasColumn('batch_parent_id', 'integer', 4, array(

            ));
        $this->hasColumn('batch_name', 'string', 128, array(
                'notnull' => true,
            ));
        $this->hasColumn('batch_desc', 'string', 256, array(

            ));
        $this->hasColumn('batch_type', 'string', 1, array(
                'fixed' => true,
                'notnull' => true,
            ));
        $this->hasColumn('batch_status', 'string', 1, array(
                'fixed' => true,
                'notnull' => true,
                'default' => self::$STATUS_ACTIVE,
            ));
        $this->hasColumn('batch_public_flag', 'boolean', null, array(
                'notnull' => true,
                'default' => false,
            ));
        $this->hasColumn('batch_cre_user', 'integer', 4, array(
                'notnull' => true,
            ));
        $this->hasColumn('batch_upd_user', 'integer', 4, array(

            ));
        $this->hasColumn('batch_cre_dtim', 'timestamp', null, array(
                'notnull' => true,
            ));
        $this->hasColumn('batch_upd_dtim', 'timestamp', null, array(

            ));

        parent::setTableDefinition();
    }


    /**
     * Set table relations
     */
    public function setUp() {
        parent::setUp();
        $this->hasOne('User', array(
                'local' => 'batch_user_id',
                'foreign' => 'user_id',
                'onDelete' => 'CASCADE',
            ));
        $this->hasMany('BatchItem', array(
                'local' => 'batch_id',
                'foreign' => 'bitem_batch_id',
            ));
        $this->hasMany('ProjectBatch as Projects', array(
                'local'  => 'batch_id',
                'foreign' => 'pb_batch_id',
                'onDelete' => 'CASCADE',
            )
        );
    }


    /**
     *
     *
     * @param array   $projects
     */
    public function add_projects(array $projects) {
        foreach ($projects as $p) {
            $pb = new ProjectBatch();
            $pb->pb_prj_id = $p->prj_id;
            $this->Projects[] = $pb;
        }
    }


    /**
     * Add custom search query (from the get param 'q')
     *
     * @param AIR2_Query $q
     * @param string  $alias
     * @param string  $search
     * @param boolean $useOr
     */
    public static function add_search_str(&$q, $alias, $search, $useOr=null) {
        $a = ($alias) ? "$alias." : "";
        $str = "(".$a."batch_name LIKE ?)";
        if ($useOr) {
            $q->orWhere($str, array("%$search%"));
        }
        else {
            $q->addWhere($str, array("%$search%"));
        }
    }


    /**
     * Add BatchItems to this batch.  The $items array will have the format:
     *  $items = array(
     *      'SRCUUID00',
     *      'SRCUUID01',
     *      array('type' => 'S', 'uuid' => 'SRCUUID02'),
     *      'SRCUUID03',
     *      array('type' => 'S', 'reltype' => 'A', 'uuid' => 'SRCANNOTID1'),
     *      array('type' => 'S', 'reltype' => 'M', 'uuid' => 'SRCMAILID1'),
     *      'SRCUUID04',
     *  );
     *
     * @param Array   $items
     * @return Array containing number insert/duplicate/invalid
     */
    public function add_items($items) {
        // track invalid and duplicate counts
        $insert_count = 0;
        $invalid_count = 0;
        $duplicate_count = 0;

        // sanity check
        if (!is_array($items)) {
            throw new Exception('add_items: $items must be an array!');
        }

        // sanity check item types, formats, and duplicates
        foreach ($items as $idx => $val) {
            if (is_array($val)) {
                if (!isset($val['uuid']) || (strlen($val['uuid']) < 1) ||
                    !isset($val['type']) || $val['type'] != $this->batch_type) {
                    $invalid_count++;
                    unset($items[$idx]);
                }
            }
        }

        // create separate list of relitems, and trim items to uuid-only
        $relitems = array();
        foreach (BatchRelated::$TYPES[$this->batch_type] as $code => $relname) {
            $relitems[$code] = array();
        }
        foreach ($items as $idx => $val) {
            if (is_array($val) && isset($val['reltype'])) {
                // check for valid reltype
                if (isset($relitems[$val['reltype']])) {
                    $relitems[$val['reltype']][] = $val['uuid'];
                }
                else {
                    $invalid_count++;
                }
                unset($items[$idx]);
            }
            elseif (is_array($val)) {
                $items[$idx] = $val['uuid'];
            }
        }

        // check for duplicates
        $old_count = count($items);
        $items = array_unique($items);
        $duplicate_count += ($old_count - count($items));
        foreach ($relitems as $reltype => $reluuids) {
            $old_count = count($reluuids);
            $relitems[$reltype] = array_unique($reluuids);
            $duplicate_count += ($old_count - count($relitems[$reltype]));
        }

        // FAST-insert everything without related items
        $uuidstr = air2_array_to_string($items);
        if (count($items) > 0 && strlen($uuidstr) > 0) {
            $result = $this->_insert_base_items($uuidstr);

            // update counts
            $insert_count += $result['ins'];
            $duplicate_count += $result['dup'];
            $invalid_count += (count($items) - $result['ins'] - $result['dup']);
        }

        // FAST-insert the related items
        foreach ($relitems as $reltype => $reluuids) {
            $uuidstr = air2_array_to_string($reluuids);
            if (count($reluuids) > 0 && strlen($uuidstr) > 0) {
                $result = $this->_insert_related_items($uuidstr, $reltype);

                // update counts
                $insert_count += $result['ins'];
                $duplicate_count += $result['dup'];
                $invalid_count += (count($reluuids) - $result['ins'] - $result['dup']);
            }
        }

        return array(
            'insert' => $insert_count,
            'duplicate' => $duplicate_count,
            'invalid' => $invalid_count,
        );
    }


    /**
     * Remove BatchItems from this batch
     *
     * @param Array|string $items list of item xids, or xid and type
     */
    public function remove_items($items) {
        $bid = $this->batch_id;
        $typ = $this->batch_type;

        // get a list of UUID's
        $itemstr = $items;
        $relitems = array();
        if (is_array($items)) {
            //create a separate list of related items
            $count = count($items);
            for ($i=0; $i<$count; $i++) {
                if (isset($items[$i]['reltype'])) {
                    $reltype = $items[$i]['reltype'];
                    if ($items[$i]['type'] == $typ && isset(BatchRelated::$TYPES[$typ][$reltype])) {
                        $relitems []= $items[$i];
                    }
                    unset($items[$i]); //remove from normal list
                }
            }
            $itemstr = air2_array_to_string($items, $typ);
        }

        // remove batch_items
        $pkcol = Batch::$TYPES[$typ]['pkcol'];
        $uuidcol = Batch::$TYPES[$typ]['uuid'];
        $tbl = Doctrine::getTable(Batch::$TYPES[$typ]['table'])->getTableName();
        $str = "DELETE from batch_item WHERE bitem_batch_id = $bid ".
            "AND bitem_type = '$typ' AND bitem_xid IN ".
            "(SELECT $pkcol FROM $tbl WHERE $uuidcol IN ($itemstr))";
        $raw = AIR2_DBManager::get_master_connection();
        if (strlen($itemstr) > 0) $raw->execute($str);

        // remove batch_relateds
        $raw->beginTransaction();
        foreach ($relitems as $ri) {
            $reltyp = $ri['reltype'];
            $relxid = $ri['uuid'];
            $str = "DELETE batch_related FROM batch_related INNER JOIN batch_item ".
                "ON brel_bitem_id = bitem_id AND brel_xid = $relxid AND ".
                "brel_type = '$reltyp' AND bitem_batch_id = $bid";
            $raw->execute($str);
        }
        $raw->commit();
    }


    /**
     * Remove all BatchItems from this batch, optionally excluding some items
     *
     * @param Array|string $excl_items items to exclude from this operation
     */
    public function remove_all($excl_items=null) {
        // get a list of UUID's
        if ($excl_items && is_array($excl_items)) {
            $excl_items = air2_array_to_string($excl_items, $this->batch_type);
        }

        $bid = $this->batch_id;
        $typ = $this->batch_type;
        $pkcol = Batch::$TYPES[$typ]['pkcol'];
        $uuidcol = Batch::$TYPES[$typ]['uuid'];
        $tbl = Doctrine::getTable(Batch::$TYPES[$typ]['table'])->getTableName();
        $str = "DELETE from batch_item WHERE bitem_batch_id = $bid";
        if ($excl_items) {
            $str .= " AND bitem_xid NOT IN ".
                "(SELECT $pkcol FROM $tbl WHERE $uuidcol IN ($excl_items))";
        }

        $raw = AIR2_DBManager::get_master_connection();
        $raw->execute($str);
    }


    /**
     * Copy the contents of other batches into this batch
     *
     * @param Array   $batch_uuids the UUIDs of the batches to copy from
     */
    public function merge($batch_uuids) {
        // if nothing to get, just return
        if (!$batch_uuids || count($batch_uuids) == 0) return;

        // get the batches to merge
        $merges = AIR2_Query::create()
        ->select('batch_id as uuid, batch_type as type')
        ->from('Batch')
        ->whereIn('batch_uuid', $batch_uuids)
        ->fetchArray();

        // build list of batch ID's
        $merge_ids = air2_array_to_string($merges, $this->batch_type);

        $bid = $this->batch_id;
        $typ = $this->batch_type;
        $usr = AIR2_REMOTE_USER_ID;
        $dtm = air2_date();
        $flds = 'bitem_batch_id, bitem_type, bitem_xid, bitem_status, '.
            'bitem_cre_user, bitem_upd_user, bitem_cre_dtim, bitem_upd_dtim';
        $sel = "$bid, '$typ', bitem_xid, 'A', $usr, $usr, '$dtm', '$dtm'";
        $str = "INSERT INTO batch_item ($flds) ".
            "SELECT $sel FROM batch_item ".
            "WHERE bitem_type = '$typ' AND bitem_batch_id IN ($merge_ids) ".
            "AND bitem_xid NOT IN ".
            "(SELECT bitem_xid FROM batch_item WHERE bitem_batch_id = $bid) ".
            "GROUP BY bitem_xid";

        $raw = AIR2_DBManager::get_master_connection();
        $raw->execute($str);
    }


    /**
     * Add the results of a search query to this batch.  Valid parameters
     * are 'i', 'q', and 'total' count of the expected result count the search
     * should return.
     *
     * @param assoc-array $params containing the search params
     */
    public function add_search($params) {
        if (!isset($params['i']) || !isset($params['q']) || !isset($params['total'])) {
            throw new Exception("Invalid parameters passed to add_search");
        }
        $typ = $this->batch_type;
        $i = $params['i'];
        $q = $params['q'];
        $total = $params['total'];

        // sanity check
        $valid_search_types = explode('|', Batch::$TYPES[$typ]['search']);
        if (!in_array($i, $valid_search_types)) {
            throw new Exception("Unable to add '$i' to batch type '$typ'!");
        }

        // call the search server
        $proxy = new Search_Proxy(
            array(
                'url' => AIR2_SEARCH_URL,
                'cookie_name' => AIR2_AUTH_TKT_NAME,
                'query' => $q,
                'params' => array(
                    'i' => $i,
                    'u' => 1, // UUIDs only
                    'limit' => $total,
                ),
            )
        );
        $rsp = $proxy->response();
        $json = json_decode($rsp['json'], true);

        if (!$json['success']) {
            throw new Exception("Search server returned error: " . $json['error']);
        }

        // check the total against the expected total
        $rsp_total = $json['total'];
        if ($rsp_total != $total) {
            throw new Exception("Search returned unexpected total! Expected ".
                "$total, got $rsp_total. Aborting operation!");
        }

        // add results to this batch
        return $this->add_items($json['results']);
    }


    /**
     * Creates new batches by getting random output from this batch.  You must
     * specify either:
     * 1) both $num and $size, to produce a number of static-sized batches
     * 2) just $num, to split this batch evenly (as possible) among batches
     * 3) just $size, to create one batch of a certain size
     *
     * @param int     $num  (optional) the number of batches to create
     * @param int     $size (optional) the size of each batch
     * @return array the id's of the created batches
     */
    public function random_output($num=null, $size=null) {
        $raw = AIR2_DBManager::get_master_connection();

        // sanity checking
        $count = $raw->fetchOne('select count(*) from batch_item where '.
            'bitem_batch_id = ?', array($this->batch_id), 0);
        if (!$num && !$size) {
            throw new Exception("Invalid parameters provided to random method!");
        }
        elseif (($num && $size && ($num * $size > $count)) ||
            ($size && ($size < 1 || $size > $count)) ||
            ($num && !$size && $num > 20)) {
            throw new Exception("Invalid size/num specified for random batches!");
        }

        $q = AIR2_Query::create()
        ->select('bitem_xid')
        ->from('BatchItem')
        ->where('bitem_batch_id = ?', $this->batch_id);

        // setup the query limit, if any
        if ($size && !$num) {
            $q->limit($size);
            $num = 1;
        }
        elseif ($num && !$size) {
            $size = ceil($count / $num);
        }
        else {
            $q->limit($num * $size);
        }

        // fetch the batch items
        $res = $q->execute(array(), Doctrine_Core::HYDRATE_SCALAR);
        $total = count($res);

        // create arrays for xids
        $bitems = array();
        for ($i=0; $i<$num; $i++) {
            $bitems[$i] = array();
        }

        // iterate through the results
        $idx = 0;
        $bidx = 0;
        while ($idx < $total) {
            // check that the batch isn't yet full
            $bcount = count($bitems[$bidx]);
            if ($bcount < $size) {
                // decide how many items to pull
                $max = min(array($size - $bcount, 10));
                $pull_num = rand(1, $max);

                // pull that many items
                for ($j=0; $j<$pull_num && $idx < $total; $j++) {
                    $xid = $res[$idx]['BatchItem_bitem_xid'];
                    $bitems[$bidx][] = $xid;
                    $idx++;
                }
            }

            //increment and wrap batch index
            $bidx = ($bidx + 1) % $num;
        }
        unset($res);

        // create the random batches
        $rand_ids = array();
        for ($i=0; $i<$num; $i++) {
            $b = new Batch();
            $b->batch_user_id = AIR2_REMOTE_USER_ID;
            $b->batch_type = $this->batch_type;
            $b->batch_public_flag = $this->batch_public_flag;
            $b->batch_status = 'A';
            $b->batch_name = "$this->batch_name - Random $i";
            $b->batch_desc = "Random output from batch: '$this->batch_name'";
            $b->save();

            // randomly grab an index into bitems
            $bitem_idx = rand(0, count($bitems) - 1);
            $bitem_count = count($bitems[$bitem_idx]);
            //SLOOOOOOW!!
            //for ($j=0; $j<$bitem_count; $j++) {
            //    $b->BatchItem[$j]->bitem_xid = $bitems[$bitem_idx][$j];
            //    $b->BatchItem[$j]->bitem_type = $b->batch_type;
            //}
            // TODO: not cross-database compatible, and will break on very large batches!!
            $usr = AIR2_REMOTE_USER_ID;
            $dtim = air2_date();
            $type = $b->batch_type;
            $bid = $b->batch_id;
            $flds = 'bitem_batch_id, bitem_type, bitem_xid, bitem_status, '.
                'bitem_cre_user, bitem_cre_dtim, bitem_upd_user, bitem_upd_dtim';
            $vals = '';
            for ($j=0; $j<$bitem_count; $j++) {
                if (strlen($vals) > 0) $vals .= ",";
                $xid = $bitems[$bitem_idx][$j];
                $vals .= "($bid,'$type',$xid,'A',$usr,'$dtim',$usr,'$dtim')";
            }
            $raw->execute("insert into batch_item ($flds) values $vals", array());

            // slice the bitem array
            array_splice($bitems, $bitem_idx, 1);
            $rand_ids []= $b->batch_id;
        }
        return $rand_ids;
    }


    /**
     *
     *
     * @param User    $user
     * @return boolean
     */
    public function user_may_read( $user ) {

        if ($user->is_system()) {
            return AIR2_AUTHZ_IS_SYSTEM;
        }

        // owner
        if ($this->batch_user_id == $user->user_id) {
            return AIR2_AUTHZ_IS_OWNER;
        }

        // group based on shared project or org assignments
        // get list of all related org_ids from ->Projects->ProjectOrg
        // and see where they overlap with $user authz (no need to check role for "read").
        // TODO refactor to execute minimal SQL.
        $creator_org_ids = array_keys($this->User->get_authz());
        $user_org_ids = array_keys($user->get_authz());
        if ($this->batch_public_flag && count(array_intersect($creator_org_ids, $user_org_ids))) {
            return AIR2_AUTHZ_IS_ORG;
        }

        $projects = $this->Projects;
        foreach ($projects as $pb) {
            if (count(array_intersect($user_org_ids, $pb->Project->get_authz()))) {
                return AIR2_AUTHZ_IS_PROJECT;
            }
        }

        return AIR2_AUTHZ_IS_DENIED;
    }


    /**
     *
     *
     * @param User    $user
     * @return boolean
     */
    public function user_may_write( $user ) {

        if ($user->is_system()) {
            return AIR2_AUTHZ_IS_SYSTEM;
        }

        if ($this->batch_user_id == $user->user_id) {
            return AIR2_AUTHZ_IS_OWNER;
        }

        // if it is brand new, just requires READER in any org
        if (!$this->batch_id) {
            $authz = $user->get_authz();
            foreach ($authz as $org_id => $role) {
                if ($role & AIR2_AUTHZ_READER) {
                    return AIR2_AUTHZ_IS_NEW;
                }
            }
        }

        // TODO confirm no other cases
        return AIR2_AUTHZ_IS_DENIED;
    }


    /**
     *
     *
     * @param User    $user
     * @return boolean
     */
    public function user_may_manage($user) {
        return $this->user_may_write($user);
    }


    /**
     * Apply authz rules for who may view the existence of a Saved Search.
     *
     * @param AIR2_Query $q     (reference)
     * @param User    $u     (reference)
     * @param string  $alias (optional)
     */
    public static function query_may_read(AIR2_Query &$q, User &$u, $alias=null) {
        if ($u->is_system()) {
            return;
        }
        $a = ($alias) ? "$alias." : "";
        $user_id = $u->user_id;

        // subqueries
        $uo_act = UserOrg::$STATUS_ACTIVE;
        $po_act = ProjectOrg::$STATUS_ACTIVE;
        $my_orgs = "select uo_org_id from user_org where uo_user_id = $user_id".
            " and uo_status = '$uo_act'";
        $my_prjs = "select porg_prj_id from project_org where porg_org_id in ($my_orgs)".
            " and porg_status = '$po_act'";
        $batch_orgs = "select uo_org_id from user_org where uo_user_id = ${a}batch_user_id".
            " and uo_status = '$uo_act'";
        $batch_prjs = "select pb_prj_id from project_batch where pb_batch_id = ${a}batch_id";

        // queries to count common orgs/projects between remote user and batch user
        $common_prjs = "select count(*) from ($my_prjs) spr where porg_prj_id in ($batch_prjs)";
        $common_orgs = "select count(*) from ($my_orgs) mor where uo_org_id in ($batch_orgs)";

        // add selects (for fun), but where condition is the important part
        $q->addSelect("($common_orgs) as shared_orgs");
        $q->addSelect("($common_prjs) as shared_prjs");
        $shared = "${a}batch_public_flag = true AND ((($common_orgs) > 0) OR (($common_prjs) > 0))";
        $q->addWhere("(${a}batch_user_id = $user_id) OR ($shared)");
    }


    /**
     * Apply authz rules for who may view the existence of a Saved Search.
     *
     * @param AIR2_Query $q     (reference)
     * @param User    $u     (reference)
     * @param string  $alias (optional)
     */
    public static function query_may_write(AIR2_Query &$q, User &$u, $alias=null) {
        if ($u->is_system()) {
            return;
        }
        $a = ($alias) ? "$alias." : "";
        $user_id = $u->user_id;

        $q->addWhere("${a}batch_user_id = ?", $user_id);

    }


    /**
     *
     *
     * @param unknown $q     (reference)
     * @param unknown $u     (reference)
     * @param unknown $alias (optional)
     * @return unknown
     */
    public static function query_may_manage(AIR2_Query &$q, User &$u, $alias=null) {
        if ($u->is_system()) {
            return;
        }
        return self::query_may_write($q, $u, $alias);
    }


    /**
     * Helper function to batch items using a string (comma separated or a sql
     * statement) to select UUID's identifying the batch items (src_uuid, etc).
     *
     * @param string  $wherein
     * @return array duplicate and insert counts
     */
    private function _insert_base_items($wherein) {
        $raw = AIR2_DBManager::get_master_connection();

        // get variables
        $bid = $this->batch_id;
        $typ = $this->batch_type;
        $usr = AIR2_REMOTE_USER_ID;
        $dtm = air2_date();
        $pkcol = Batch::$TYPES[$typ]['pkcol'];
        $uuidcol = Batch::$TYPES[$typ]['uuid'];
        $stat = BatchItem::$STATUS_ACTIVE;
        $tbl = Doctrine::getTable(Batch::$TYPES[$typ]['table'])->getTableName();

        // build count-dups and insert statements
        $flds = 'bitem_batch_id, bitem_type, bitem_xid, bitem_status, '.
            'bitem_cre_user, bitem_upd_user, bitem_cre_dtim, bitem_upd_dtim';
        $sel = "$bid, '$typ', $pkcol, '$stat', $usr, $usr, '$dtm', '$dtm'";
        $insert = "INSERT IGNORE INTO batch_item ($flds) SELECT $sel FROM".
            " $tbl WHERE $uuidcol IN ($wherein)";
        $dup = "SELECT count(*) FROM batch_item WHERE bitem_batch_id = $bid".
            " AND bitem_xid IN (SELECT $pkcol FROM $tbl WHERE $uuidcol IN".
            " ($wherein))";

        // execute raw sql
        $dups = $raw->fetchOne($dup, array(), 0);
        $result = $raw->execute($insert);
        return array(
            'ins' => $result->rowCount(),
            'dup' => $dups,
        );
    }


    /**
     * Helper function to insert related items of a certain type into this
     * batch.  The $wherein string should identify each related item, such as
     * srcan_id's, smadd_id's, etc.
     *
     * @param string  $wherein
     * @param char    $reltype
     * @return array duplicate and insert counts
     */
    private function _insert_related_items($wherein, $reltype) {
        $raw = AIR2_DBManager::get_master_connection();

        // get base variables
        $bid = $this->batch_id;
        $typ = $this->batch_type;
        $usr = AIR2_REMOTE_USER_ID;
        $dtm = air2_date();
        $pkcol = Batch::$TYPES[$typ]['pkcol'];
        $uuidcol = Batch::$TYPES[$typ]['uuid'];
        $doctbl = Doctrine::getTable(Batch::$TYPES[$this->batch_type]['table']);
        $tbl = $doctbl->getTableName();

        // get related variables
        $stat = BatchRelated::$STATUS_ACTIVE;
        $relobj = $doctbl->getRelation(BatchRelated::$TYPES[$typ][$reltype]);
        $reldoctbl = $relobj->getTable();
        $reltbl = $reldoctbl->getTableName();
        $relfkey = $relobj->getForeignColumnName();
        $relkey = array_shift($reldoctbl->getIdentifierColumnNames());
        $reluuidcol = air2_get_model_uuid_col($reldoctbl->getClassnameToReturn());

        // insert all the primary items -- ignore duplicates
        $pkeys = "SELECT $relfkey FROM $reltbl WHERE $reluuidcol IN ($wherein)";
        $base_uuids = "SELECT $uuidcol FROM $tbl WHERE $pkcol IN ($pkeys)";
        $result = $this->_insert_base_items($base_uuids);

        // insert related items
        $flds = 'brel_bitem_id, brel_type, brel_xid, brel_status, '.
            'brel_cre_user, brel_upd_user, brel_cre_dtim, brel_upd_dtim';
        $sel = "bitem_id, '$reltype', $relkey, '$stat', $usr, $usr, '$dtm', '$dtm'";
        $join = "SELECT $sel FROM $reltbl INNER JOIN batch_item ON".
            " $relfkey = bitem_xid WHERE bitem_batch_id = $bid AND".
            " $reluuidcol IN ($wherein)";

        $insert = "INSERT IGNORE INTO batch_related ($flds) $join";
        $dup = "SELECT count(*) FROM batch_related WHERE brel_bitem_id IN".
            " (SELECT bitem_id FROM batch_item WHERE bitem_batch_id = $bid)".
            " AND brel_type = '$reltype' AND brel_xid IN (SELECT $relkey FROM".
            " $reltbl WHERE $reluuidcol IN ($wherein))";

        // execute raw sql
        $dups = $raw->fetchOne($dup, array(), 0);
        $result = $raw->execute($insert);
        return array(
            'ins' => $result->rowCount(),
            'dup' => $dups,
        );
    }


}
